import time
import json
import sqlite3
from datetime import datetime

from flask_cors import CORS
from flask import Flask, request
from selenium import webdriver
from selenium.webdriver.chrome.service import Service
from selenium.webdriver.common.by import By

# 1. 需要安装 vncserver，yum install tigervnc-server
# 2. 启动 vncserver
# 3. 设置环境变量，export DISPLAY=:1
# 4. 启动爬虫服务


app = Flask('51-tracking-service')
CORS(app)


def create_table():
    try:
        conn = sqlite3.connect('51tracking.db')

        cursor = conn.cursor()
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS rcf_result (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                awb TEXT NOT NULL UNIQUE,
                rcf TEXT NOT NULL,
                create_time TEXT NOT NULL
            )
        ''')

        conn.commit()
    except Exception as e:
        print(e.__str__())

        if conn:
            conn.rollback()
    finally:
        if cursor:
            cursor.close()

        if conn:
            conn.close()


# createTime: 2025-07-23T12:30:00
def get_awb_by_create_time(createTime):
    try:
        conn = sqlite3.connect('51tracking.db')

        cursor = conn.cursor()

        cursor.execute('SELECT awb, rcf, create_time FROM rcf_result WHERE create_time > "' + createTime + '"')
        rows = cursor.fetchall()
        awbArray = []
        for row in rows:
            awbArray.append({
                'awb': row[0],
                'rcf': row[1],
                'createTime': row[2]
            })

        return {
            'msg': 'SUCCESS',
            'awbs': awbArray
        }
    except Exception as e:
        print(e.__str__())

        if conn:
            conn.rollback()

        return {
            'msg': 'FAILED',
            'desc': e.__str__()
        }
    finally:
        if cursor:
            cursor.close()

        if conn:
            conn.close()


# createTime: 2025-07-23T12:30:00
def delete_awb_by_create_time(createTime):
    try:
        conn = sqlite3.connect('51tracking.db')

        cursor = conn.cursor()

        cursor.execute('DELETE FROM rcf_result WHERE create_time < "' + createTime + '"')

        conn.commit()

        return {
            "msg": "SUCCESS"
        }
    except Exception as e:
        print(e.__str__())

        if conn:
            conn.rollback()

        return {
            'msg': 'FAILED',
            'desc': e.__str__()
        }
    finally:
        if cursor:
            cursor.close()

        if conn:
            conn.close()


def update_rcf_by_awb(awb, rcf):
    try:
        conn = sqlite3.connect('51tracking.db')

        cursor = conn.cursor()

        current_time = datetime.now().isoformat()
        cursor.execute('''
            INSERT OR REPLACE INTO rcf_result (awb, rcf, create_time)
            VALUES (?, ?, ?)
        ''', (awb, rcf, current_time))

        conn.commit()

        return {
            'msg': 'SUCCESS'
        }
    except Exception as e:
        print(e.__str__())

        if conn:
            conn.rollback()

        return {
            'msg': 'FAILED',
            'desc': e.__str__()
        }
    finally:
        if cursor:
            cursor.close()

        if conn:
            conn.close()


def get_awb(awb):
    try:
        conn = sqlite3.connect('51tracking.db')

        cursor = conn.cursor()

        cursor.execute('SELECT awb, rcf, create_time FROM rcf_result WHERE awb ="' + awb + '"')
        row = cursor.fetchone()
        if row:
            return {
                'msg': 'SUCCESS',
                'info': {
                    'awb': row[0],
                    'rcf': row[1],
                    'createTime': row[2]
                }
            }
        else:
            return {
                'msg': 'SUCCESS',
                'awb': {
                }
            }
    except Exception as e:
        print(e.__str__())

        if conn:
            conn.rollback()

        return {
            'msg': 'FAILED',
            'desc': e.__str__()
        }
    finally:
        if cursor:
            cursor.close()

        if conn:
            conn.close()


def insert_awb(awbArray):
    try:
        conn = sqlite3.connect('51tracking.db')

        cursor = conn.cursor()

        current_time = datetime.now().isoformat()
        for awb in awbArray:
            cursor.execute('INSERT INTO rcf_result (awb, rcf, create_time) VALUES (?, ?, ?)',
                           (awb, '', current_time))

        conn.commit()

        return {
            'msg': 'SUCCESS'
        }
    except Exception as e:
        print(e.__str__())

        if conn:
            conn.rollback()

        return {
            'msg': 'FAILED',
            'desc': e.__str__()
        }
    finally:
        if cursor:
            cursor.close()

        if conn:
            conn.close()


def init_scrapy():
    create_table()


@app.route('/getAllRcf', methods=['GET'])
def getAllRcf():
    data = json.loads(json.dumps(request.get_json()))

    resp = scrapy51TrackingByAwb(data['awbs'])

    return resp


@app.route('/insertAwbs', methods=['POST'])
def insertAwbs():
    data = json.loads(json.dumps(request.get_json()))

    return insert_awb(data['awbs'])


@app.route('/deleteAwbs', methods=['POST'])
def deleteAwbs():
    data = json.loads(json.dumps(request.get_json()))

    return delete_awb_by_create_time(data['createTime'])


@app.route('/getAwbs', methods=['GET'])
def getAwbs():
    data = json.loads(json.dumps(request.get_json()))

    return get_awb_by_create_time(data['createTime'])


@app.route('/getAwb', methods=['GET'])
def getAwb():
    data = json.loads(json.dumps(request.get_json()))

    return get_awb(data['awb'])


def createChromeDriver():
    chrome_options = webdriver.ChromeOptions()
    chrome_options.add_argument('--headless')
    chrome_options.add_argument('--no-sandbox')
    chrome_options.add_argument('--disable-dev-shm-usage')
    # 将--user-data-dir改为自定义目录
    # 在linux服务器上执行，需要增加
    # chrome_options.add_argument("--user-data-dir=/root")

    s = Service(executable_path='/Users/gilbert/chromedriver-mac-x64/chromedriver')
    _chrome_driver_instance = webdriver.Chrome(service=s, options=chrome_options)

    _chrome_driver_instance.maximize_window()

    return _chrome_driver_instance


def updateRcf(awb, pcs, weight, rcfArray, nfdArray):
    if awb.startswith("17"):
        total = 0
        time_str = "2023-07-23 13:45:00"
        time_format = "%Y-%m-%d %H:%M:%S"
        max_time = datetime.strptime(time_str, time_format)
        for rcf in rcfArray:
            rcf_time = datetime.strptime(rcf['timestamp'], time_format)
            if rcf_time > max_time:
                max_time = rcf_time
            total += int(rcf['pcs'])

        if total >= int(pcs):
            max_time_str = max_time.strftime('%Y-%m-%dT%H:%M:%S')
            print("更新AWB的RCF：" + awb + " " + max_time_str)
            update_rcf_by_awb(awb, max_time_str)

    if awb.startswith("9") or awb.startswith("78"):
        if len(nfdArray) > 0:
            time_str = "2023-07-23 13:45:00"
            time_format = "%Y-%m-%d %H:%M:%S"
            max_time = datetime.strptime(time_str, time_format)
            for nfd in nfdArray:
                nfd_time = datetime.strptime(nfd['timestamp'], time_format)
                if nfd_time > max_time:
                    max_time = nfd_time

            max_time_str = max_time.strftime('%Y-%m-%dT%H:%M:%S')
            print("更新AWB的RCF：" + awb + " " + max_time_str)
            update_rcf_by_awb(awb, max_time_str)


def scrapy51TrackingByAwb(awbArrays):
    chrome_driver_instance = createChromeDriver()

    try:
        results = []
        for awb in awbArrays:
            chrome_driver_instance.get("https://www.51tracking.com/aircargo/cn/" + awb)

            time.sleep(20)

            source = chrome_driver_instance.find_element(By.XPATH, "//tbody[tr[1]/td[1]='始发站']/tr[2]/td[1]").text
            target = chrome_driver_instance.find_element(By.XPATH, "//tbody[tr[1]/td[1]='始发站']/tr[2]/td[2]").text
            weight = chrome_driver_instance.find_element(By.XPATH, "//tbody[tr[1]/td[1]='始发站']/tr[2]/td[3]").text
            pcs = chrome_driver_instance.find_element(By.XPATH, "//tbody[tr[1]/td[1]='始发站']/tr[2]/td[4]").text

            notifies = chrome_driver_instance.find_elements(By.XPATH, "//tr[td[1]='NFD']")
            notify_labels = []
            if notifies:
                for notify in notifies:
                    notify_time_text = notify.find_element(By.XPATH, ".//td[2]").text
                    notify_labels.append({
                        'timestamp': notify_time_text
                    })

            delivers = chrome_driver_instance.find_elements(By.XPATH, "//tr[td[1]='DLV']")
            deliver_labels = []
            for deliver in delivers:
                dlv_time_text = deliver.find_element(By.XPATH, ".//td[2]").text
                dlv_event_text = deliver.find_element(By.XPATH, ".//td[3]").text
                dlv_location_text = deliver.find_element(By.XPATH, ".//td[4]").text
                dlv_flight_text = deliver.find_element(By.XPATH, ".//td[5]").text
                dlv_weight_text = deliver.find_element(By.XPATH, ".//td[6]").text
                dlv_pcs_text = deliver.find_element(By.XPATH, ".//td[7]").text
                dlv_checkpoint_text = deliver.find_element(By.XPATH, ".//td[8]").text
                deliver_labels.append({
                    'timestamp': dlv_time_text,
                    'event': dlv_event_text,
                    'location': dlv_location_text,
                    'flight': dlv_flight_text,
                    'weight': dlv_weight_text,
                    'pcs':  dlv_pcs_text,
                    'checkpoint':  dlv_checkpoint_text
                })

            receives = chrome_driver_instance.find_elements(By.XPATH, "//tr[td[1]='RCF']")
            receive_labels = []
            for receive in receives:
                rcf_time_text = receive.find_element(By.XPATH, ".//td[2]").text
                rcf_event_text = receive.find_element(By.XPATH, ".//td[3]").text
                rcf_location_text = receive.find_element(By.XPATH, ".//td[4]").text
                rcf_flight_text = receive.find_element(By.XPATH, ".//td[5]").text
                rcf_weight_text = receive.find_element(By.XPATH, ".//td[6]").text
                rcf_pcs_text = receive.find_element(By.XPATH, ".//td[7]").text
                rcf_checkpoint_text = receive.find_element(By.XPATH, ".//td[8]").text
                receive_labels.append({
                    'timestamp': rcf_time_text,
                    'event': rcf_event_text,
                    'location': rcf_location_text,
                    'flight': rcf_flight_text,
                    'weight': rcf_weight_text,
                    'pcs': rcf_pcs_text,
                    'checkpoint': rcf_checkpoint_text
                })

            resp = {
                'awb': awb,
                'source': source,
                'target': target,
                'weight': weight,
                'pcs': pcs,
                'rcfs': receive_labels,
                'dlvs': deliver_labels,
                'nfds': notify_labels
            }

            updateRcf(awb, pcs, weight, receive_labels, notify_labels)

            results.append(resp)

        response = {
            "msg": "SUCCESS",
            "results": results
        }

        return response
    except Exception as e:
        print(e.__str__())

        return {
            'msg': 'FAILED',
            'desc': '打开网页超时或者元素不存在',
            'exception': e.__str__()
        }
    finally:
        chrome_driver_instance.quit()


if __name__ == '__main__':
    init_scrapy()
    app.run(host='0.0.0.0', port=10010, debug=False)
